package com.studyv3.test.mapper;

import com.studyv3.common.core.mapper.BaseMapperPlus;
import com.studyv3.test.domain.Stock;
import com.studyv3.test.domain.vo.StockApplyWasteVo;
import com.studyv3.test.domain.vo.StockVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * lb_库存表 Mapper 接口
 * </p>
 *
 * @author author
 * @since 2024-05-06
 */
public interface StockMapper extends BaseMapperPlus<StockMapper, Stock, StockVo> {

    @Select("<script>select s.*,p.name as productsName,b.batchName " +
            "from lb_stock s " +
            "left join (select id,name from lb_products )p on s.products_id=p.id " +
            "left join (select id as batchId,name as batchName from lb_batch) b on b.batchId=s.batch_id" +
            "<where> " +
            "<if test=\"productsId !=null and productsId !=''\"> and p.id = #{productsId} </if> " +
            "<if test=\"batchId !=null and batchId !=''\"> and b.batchId = #{batchId} </if> " +
            "<if test=\"productsName !=null and productsName !=''\"> and p.name like concat('%', #{productsName}, '%') </if> " +
            "<if test=\"batchName !=null and batchName !=''\"> and b.batchName like concat('%', #{batchName}, '%') </if> </where></script>")
    List<Stock> stockList(Stock stock);

    @Select("<script>select * from(select p.id as products_id,name as productsName,warn_num,ifnull(s.products_num,0) products_num " +
            "from lb_products p " +
            "left join " +
            "(select sum(products_num) products_num , products_id  from lb_stock group by products_id) s " +
            "on p.id=s.products_id)a " +
            "<where> " +
            "<if test=\"productsName !=null and productsName !=''\"> and a.productsName like concat('%', #{productsName}, '%') </if> " +
            "</where></script>")
    List<Stock> listwithnobatch(Stock stock);

    @Select("<script>select s.*,p.name as productsName,b.batchName, la.id as applyWasteId,la.flag " +
            "from lb_stock s " +
            "left join (select id,name from lb_products )p on s.products_id=p.id " +
            "left join (select id as batchId,name as batchName from lb_batch) b on b.batchId=s.batch_id " +
            "left join (select id,products_id,batch_id,flag from lb_apply_waste where applicant = #{applicant} and deleted = 0) la on s.batch_id = la.batch_id and s.products_id = la.products_id "+
            "<where> " +
            "<if test=\"productsId !=null and productsId !=''\"> and p.id = #{productsId} </if> " +
            "<if test=\"batchId !=null and batchId !=''\"> and b.batchId = #{batchId} </if> " +
            "<if test=\"productsName !=null and productsName !=''\"> and p.name like concat('%', #{productsName}, '%') </if> " +
            "<if test=\"batchName !=null and batchName !=''\"> and b.batchName like concat('%', #{batchName}, '%') </if> </where></script>")
    List<StockApplyWasteVo> listStockApplyWaste(Stock stock);

    @Select("<script>select s.*,p.name as productsName,b.batchName, la.id as applyWasteId,la.applicant ,la.flag, la.object_amount " +
            "from lb_stock s " +
            "left join (select id,name from lb_products )p on s.products_id=p.id " +
            "left join (select id as batchId,name as batchName from lb_batch) b on b.batchId=s.batch_id " +
            "left join (select id,products_id,batch_id,applicant,flag,object_amount from lb_apply_waste where deleted = 0) la on s.batch_id = la.batch_id and s.products_id = la.products_id "+
            "<where> " +
            "<if test=\"applicant !=null and applicant !=''\"> and la.applicant = #{applicant} </if> " +
            "<if test=\"flag !=null and flag !=''\"> and la.flag = #{flag} </if> " +
            "<if test=\"productsId !=null and productsId !=''\"> and p.id = #{productsId} </if> " +
            "<if test=\"batchId !=null and batchId !=''\"> and b.batchId = #{batchId} </if> " +
            "<if test=\"productsName !=null and productsName !=''\"> and p.name like concat('%', #{productsName}, '%') </if> " +
            "<if test=\"batchName !=null and batchName !=''\"> and b.batchName like concat('%', #{batchName}, '%') </if> </where></script>")
    List<StockApplyWasteVo> listStockApplyWasteForManager(Stock stock);
}
